﻿using CodeBuilder.Core.Domain.Models;

using Tszy.Unlimited.Base.Features;
using Tszy.Unlimited.Data.Features;
using Tszy.Unlimited.Data.FreeSQL;

namespace CodeBuilder.Core.Features.Tables
{
    internal class GetTable
    {
        public record Request(string DatabaseName, string TableName) : FunctionRecordFeatureRequest<Response>;

        public class Response
        {
            public IEnumerable<CodeTable> Tables { get; set; } = new List<CodeTable>();
        }

        public class Handler : FreeSQLTransactionFunctionRecordFeatureHandler<Request, Response>
        {
            private readonly IFreeSQLRepository<CodeTable> _tableRepository;

            public Handler(IFreeSQLRepository<CodeTable> tableRepository, IFreeSQLUnitOfWorkFactory unitOfWorkFactory, bool transaction = true) : base(unitOfWorkFactory, transaction)
            {
                _tableRepository = tableRepository;
                response = new Response();
            }

            /// <summary>
            ///
            /// </summary>
            /// <param name="databaseName">数据库名。</param>
            /// <returns></returns>
            private async Task<IEnumerable<CodeTable>> GetTablesAsync(string databaseName)
            {
                var sql = $"SELECT TABLE_NAME AS 'TableName', TABLE_COMMENT AS 'Description' FROM information_schema.tables WHERE TABLE_SCHEMA = '{databaseName.Trim()}'";

                return await _tableRepository.Orm.Select<CodeTable>().WithSql(sql).ToListAsync();
            }

            private async Task<IEnumerable<CodeField>> GetFiledsAsync(string databaseName, string tableName)
            {
                var sql = $"SELECT COLUMN_NAME AS 'FieldName', DATA_TYPE AS 'FieldType', COLUMN_COMMENT AS 'Description', COLUMN_KEY AS 'Key', CHARACTER_MAXIMUM_LENGTH AS 'FieldSize' FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME='{tableName}' AND TABLE_SCHEMA = '{databaseName.Trim()}' ORDER BY ORDINAL_POSITION";

                return await _tableRepository.Orm.Select<CodeField>().WithSql(sql).ToListAsync();
            }

            protected override async Task OnExecuteAsync(Request request, CancellationToken cancellationToken)
            {
                var tables = await GetTablesAsync(request.DatabaseName);

                if (tables.Any())
                {
                    foreach (var table in tables)
                    {
                        var fields = await GetFiledsAsync(request.DatabaseName, table.TableName);

                        if (fields.Any())
                        {
                            table.Fields.AddRange(fields);
                        }
                    }
                }

                response!.Tables = tables.Where(table => table.TableName.Contains(request.TableName)) ?? new List<CodeTable>();
            }
        }
    }
}